package com.css.fxfzfxqh.modules.reportmanagement.repository.impl;

import com.css.fxfzfxqh.modules.lifelineEngineering.entity.LifelineTaskEntity;
import com.css.fxfzfxqh.modules.reportmanagement.repository.BuildingDataRepository;
import com.css.fxfzfxqh.modules.reportmanagement.vo.BuildingVO;
import com.css.fxfzfxqh.util.PlatformObjectUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Description: fxfzfxqh
 * @Author: lhl
 * @CreateDate: 2023/11/13 10:33
 */
@Repository
public class BuildingDataRepositoryImpl implements BuildingDataRepository {
    @Resource
    private JdbcTemplate jdbcTemplate;

//    @Override
//    public Map<String, Object> getAll(BuildingVO param, int curPage, int pageSize) {
//        int firstIndex = (curPage - 1) * pageSize;
//        int lastIndex = pageSize;
//        StringBuilder limitSql = new StringBuilder(" limit " + lastIndex + " offset " + firstIndex);
//        StringBuilder sql = new StringBuilder("select id,assess_code as code,assess_name as name,'建筑物评估' as pglb," +
//                "assess_type as assessType,seismic_input_mode as inputMethod,assess_province as province," +
//                "assess_city as city,assess_county as county,role FROM fxqh_calculation_tasks WHERE del_flag='0'");
//        StringBuilder countSql = new StringBuilder("select count(id) FROM fxqh_calculation_tasks WHERE del_flag='0' ");
//        if (PlatformObjectUtils.isNotEmpty(param.getProvince())) {
//            sql.append(" and assess_province ='" + param.getProvince() + "'");
//            countSql.append(" and assess_province ='" + param.getProvince() + "'");
//        }
//        if (PlatformObjectUtils.isNotEmpty(param.getCity())) {
//            sql.append(" and assess_city ='" + param.getCity() + "'");
//            countSql.append(" and assess_city ='" + param.getCity() + "'");
//        }
//            if (PlatformObjectUtils.isNotEmpty(param.getCounty())) {
//                sql.append(" and assess_county ='" + param.getCounty() + "'");
//                countSql.append(" and assess_county ='" + param.getCounty() + "'");
//            }
//        if (PlatformObjectUtils.isNotEmpty(param.getAssessType())) {
//            sql.append(" and assess_type like '%" + param.getAssessType() + "%'");
//            countSql.append(" and assess_type like '%" + param.getAssessType() + "%'");
//        }
//        if (PlatformObjectUtils.isNotEmpty(param.getAssessCode())) {
//            sql.append(" and assess_code like '%" + param.getAssessCode() + "%'");
//            countSql.append(" and assess_code like '%" + param.getAssessCode() + "%'");
//        }
//        if (PlatformObjectUtils.isNotEmpty(param.getAssessName())) {
//            sql.append(" and assess_name like '%" + param.getAssessName() + "%'");
//            countSql.append(" and assess_name like '%" + param.getAssessName() + "%'");
//        }
//        if (!param.getLevel()) {
//            sql.append(" and role ='" + param.getRole() + "'");
//            countSql.append(" and role ='" + param.getRole() + "'");
//        }
//        StringBuilder orderSql = new StringBuilder(" order by role asc,create_time desc");
//        sql.append(orderSql);
//        Integer total = jdbcTemplate.queryForObject(countSql.toString(), Integer.class);
//        List<Map<String, Object>> mapList = jdbcTemplate.queryForList(sql.append(limitSql).toString());
//        for (Map<String, Object> map : mapList) {
//            String role = map.get("role")+"";
//            if ("0".equals(role)){
//                map.put("province", null);
//                map.put("city", null);
//                map.put("county", null);
//            }
//            if ("1".equals(role)){
//                map.put("city", null);
//                map.put("county", null);
//            }
//            if ("2".equals(role)){
//                map.put("county", null);
//            }
//        }
//        Map<String, Object> map = new HashMap();
//        map.put("total", total);
//        map.put("rows", mapList);
//        return map;
//    }
    @Override
    public Map<String, Object> getAll(BuildingVO param, int curPage, int pageSize) {
        int firstIndex = (curPage - 1) * pageSize;
        int lastIndex = pageSize;
        StringBuilder limitSql = new StringBuilder(" limit " + lastIndex + " offset " + firstIndex);
        StringBuilder sql = new StringBuilder("select task.id,task.assess_code as code,task.assess_name as name,'建筑物评估' as pglb," +
                "task.assess_type as assessType,task.seismic_input_mode as inputMethod,task.role, array_to_string(" +
                "ARRAY (" +
                "SELECT DISTINCT UNNEST (" +
                "string_to_array( string_agg ( top.NAME, ',' ), ',' ) ) ), ',' ) AS county," +
                "array_to_string( " +
                "ARRAY ( " +
                "SELECT DISTINCT UNNEST (" +
                "string_to_array( string_agg ( top.pp_name, ',' ), ',' ) ) ), ',' ) AS province," +
                "array_to_string(" +
                "ARRAY ( " +
                "SELECT DISTINCT UNNEST ( " +
                "string_to_array(string_agg ( top.city_name, ',' ), ',' ) ) ), ',' ) AS city FROM fxqh_calculation_tasks task " +
                " LEFT JOIN fxqh_assess_toponym top ON top.assess_id = task.ID " +
                " AND task.del_flag = '0' where 1=1 ");
        StringBuilder countSql = new StringBuilder("select count(distinct(task.id)) FROM fxqh_calculation_tasks task LEFT JOIN fxqh_assess_toponym top ON top.assess_id = task.ID AND task.del_flag = '0' where 1=1 ");
        StringBuilder havingSql = new StringBuilder(" having ");
        if (PlatformObjectUtils.isNotEmpty(param.getProvince())) {
            havingSql.append("  array_to_string( " + "ARRAY (" + "SELECT DISTINCT UNNEST ( " + "string_to_array( string_agg ( top.pp_name, ',' ), ',' ) ) ), ',' ) LIKE '%").append(param.getProvince()).append("%'");
            //havingSql.append(" and assess_province ='" + param.getProvince() + "'");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getCity())) {
            havingSql.append(" and array_to_string( " + "ARRAY (" + "SELECT DISTINCT UNNEST ( " + "string_to_array( string_agg (  top.city_name, ',' ), ',' ) ) ), ',' ) LIKE '%").append(param.getCity()).append("%'");
            //havingSql.append(" and assess_city ='" + param.getCity() + "'");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getCounty())) {
            havingSql.append(" and array_to_string( " +
                    "ARRAY (" +
                    "SELECT DISTINCT UNNEST ( " +
                    "string_to_array( string_agg ( top.name, ',' ), ',' ) ) ), ',' ) LIKE '%"+param.getCounty()+"%'");
           // havingSql.append(" and assess_county ='" + param.getCounty() + "'");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getAssessType())) {
            sql.append(" and task.assess_type like '%").append(param.getAssessType()).append("%'");
            countSql.append(" and task.assess_type like '%").append(param.getAssessType()).append("%'");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getAssessCode())) {
            sql.append(" and task.assess_code like '%").append(param.getAssessCode()).append("%'");
            countSql.append(" and task.assess_code like '%").append(param.getAssessCode()).append("%'");
        }
        if (PlatformObjectUtils.isNotEmpty(param.getAssessName())) {
            sql.append(" and task.assess_name like '%").append(param.getAssessName()).append("%'");
            countSql.append(" and task.assess_name like '%").append(param.getAssessName()).append("%'");
        }
        if (!param.getLevel()) {
            sql.append(" and task.role ='").append(param.getRole()).append("'");
            countSql.append(" and task.role ='").append(param.getRole()).append("'");
        }
        StringBuilder orderSql = new StringBuilder(" order by task.create_time desc");
        sql.append(" GROUP BY" +
                " task.ID, " +
                " task.assess_code, " +
                " task.assess_name, " +
                " pglb, " +
                " task.assess_type, " +
                " task.seismic_input_mode, " +
                " task.assess_province, " +
                " task.assess_city, " +
                " task.assess_county, " +
                " task.ROLE,task.create_time ");
        if(!havingSql.toString().trim().equals("having")){
            sql.append(havingSql);
        }
        Integer total = jdbcTemplate.queryForList(sql.toString()).size();
        //Integer total = jdbcTemplate.queryForObject(countSql.toString(), Integer.class);
        List<Map<String, Object>> mapList = jdbcTemplate.queryForList(sql.append(orderSql).append(limitSql).toString());
//        for (Map<String, Object> map : mapList) {
//            String role = map.get("role")+"";
//            if ("0".equals(role)){
//                map.put("province", null);
//                map.put("city", null);
//                map.put("county", null);
//            }
//            if ("1".equals(role)){
//                map.put("city", null);
//                map.put("county", null);
//            }
//            if ("2".equals(role)){
//                map.put("county", null);
//            }
//        }
        Map<String, Object> map = new HashMap();
        map.put("total", total);
        map.put("rows", mapList);
        return map;
    }
}
